package com.corecloud.mapper.mysql.statisticalCenter;


import com.corecloud.entity.statisticalCenter.SchoolGradationEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 学校信息统计
 */
@Mapper
public interface StatisticalSchoolMapper {

    // 学校层次统计
    @Select({"<script>select year_series.year,secondary_vocational.secondaryVocational,higher_vocational.higherVocational,(secondary_vocational.secondaryVocational+higher_vocational.higherVocational) sum\n" +
            "from\n" +
            "  (\n" +
            " <choose> " +
            " <when test=\" years != null \"> " +
            " SELECT #{dataYearEnd} as year " +
            " <foreach collection='years' item='yearData' index='index'>" +
            "  UNION SELECT #{yearData} \n" +
            " </foreach> " +
            " </when> " +
            "<otherwise>\n" +
            "    SELECT YEAR(CURDATE()) AS year\n" +
            "    UNION SELECT YEAR(CURDATE() - INTERVAL 1 YEAR)\n" +
            "    UNION SELECT YEAR(CURDATE() - INTERVAL 2 YEAR)\n" +
            "    UNION SELECT YEAR(CURDATE() - INTERVAL 3 YEAR)\n" +
            "    UNION SELECT YEAR(CURDATE() - INTERVAL 4 YEAR)\n" +
            "    UNION SELECT YEAR(CURDATE() - INTERVAL 5 YEAR)\n" +
            "</otherwise>" +
            " </choose> " +
            "  ) AS year_series\n" +
            "\tleft join (select DATE_FORMAT(school.create_time,'%Y') year,count(*) secondaryVocational from sys_school school left join sys_area_for_statistics area on school.area_id = area.area_id\n" +
            "\t\t\twhere \n" +
            "\t\t\t\tschool.is_valid = 1\n" +
            "\t\t\t\tand school.school_level = 1\n" +
            " <if test=\"areaId != null and areaId != ''\"> and area.area_id = #{areaId} or area.area_parent_id = #{areaId} </if> " +
            "\t\t\tGROUP BY year\n" +
            "\t\t) secondary_vocational on secondary_vocational.year = year_series.year\n" +
            "\tleft join (select DATE_FORMAT(school.create_time,'%Y') year,count(*) higherVocational from sys_school school left join sys_area_for_statistics area on school.area_id = area.area_id\n" +
            "\t\t\twhere\n" +
            "\t\t\t\tschool.is_valid = 1\n" +
            "\t\t\t\tand school.school_level = 2\n" +
            " <if test=\"areaId != null and areaId != ''\"> and area.area_id = #{areaId} or area.area_parent_id = #{areaId} </if> " +
            "\t\t\tGROUP BY year\n" +
            "\t\t) higher_vocational on higher_vocational.year = year_series.year\n" +
            " where\n" +
            "\t1 = 1\n" +
            "\t<if test=\"dataYearStart != null and dataYearStart != '' and dataYearEnd != null and dataYearEnd != '' \"> and year_series.year &gt;= #{dataYearStart} and year_series.year &lt;= #{dataYearEnd} </if>" +
            "ORDER BY year desc",
            "</script>"})
    List<SchoolGradationEntity> schoolGradationYears(SchoolGradationEntity param);

    // 学校层次统计（按地市）
    @Select({"<script>" +
            "select  " +
            " areaCity.area_id,areaCity.area_name areaName, " +
            " sum( IF ( school.school_level = '1', 1, 0 ) ) as secondaryVocational, " +
            " sum( IF ( school.school_level = '2', 1, 0 ) ) as higherVocational, " +
            " SUM(IF(school.school_level IN ('1', '2'), 1, 0)) sum " +
            " from " +
            " (select * from sys_area_for_statistics where sys_area_for_statistics.area_parent_id = '410000') areaCity " +
            " left join sys_area_for_statistics area on areaCity.area_id = area.area_parent_id or area.area_id = areaCity.area_id " +
            " left join sys_school school on school.area_id = area.area_id and school.is_valid=1 " +
            " <if test=\" year != null and year != '' \"> and DATE_FORMAT(school.create_time,'%Y') &lt;= #{year} </if> " +
            " GROUP BY areaCity.area_id " +
            " ORDER BY " +
            " sum DESC" +
            "</script>"})
    List<SchoolGradationEntity> schoolGradationAreas(SchoolGradationEntity info);


}
